<<finishing message this time, after accidentally sending before>>
I have a couple queries in a PL/PGSQL function which execute very slowly (around
one minute each) which execute in .5 second when not executed from within the
function. Is there any way to determine why this is happening? I couldn't
figure out how to run EXPLAIN ANALYZE from within the function. The queries
aren't very complicated.
Here is an example query and plan. About 240K rows in x_rbl_ips, 7.5M rows in
filter_ips. This query returns no rows.
explain analyze SELECT DISTINCT i_ip
FROM x_rbl_ips
LEFT JOIN filter_ips ON x_rbl_ips.i_ip = filter_ips.i_filter_ip
WHERE x_rbl_ips.dts_last_modified > '2004-02-18 22:24:15.901689+00'
AND filter_ips.i_filter_ip IS NOT NULL
AND (i_filter_type_flags & X'02000000'::integer) <> X'02000000'::integer
QUERY PLAN
Unique (cost=5703.70..5703.81 rows=23 width=4) (actual time=524.439..524.439
rows=0 loops=1)
-> Sort (cost=5703.70..5703.75 rows=23 width=4) (actual
time=524.438..524.438 rows=0 loops=1)
Sort Key: x_rbl_ips.i_ip
-> Nested Loop (cost=0.00..5703.18 rows=23 width=4) (actual
time=524.028..524.028 rows=0 loops=1)
-> Seq Scan on x_rbl_ips (cost=0.00..5616.56 rows=23 width=4)
(actual time=412.738..415.842 rows=1738 loops=1)
Filter: (dts_last_modified > '2004-02-18
22:44:15.901689'::timestamp without time zone)
-> Index Scan using filter_ips_source_ip on filter_ips
(cost=0.00..3.75 rows=1 width=4) (actual time=0.061..0.061 rows=0 loops=1738)
Index Cond: ("outer".i_ip = filter_ips.i_filter_ip)
Filter: ((i_filter_ip IS NOT NULL) AND ((i_filter_type_flags
& 33554432) <> 33554432))
Total runtime: 524.868 ms
When run from within a function, this query takes about 1 minute. The hardcoded
date is substituted with a timestamp variable. If I remove the last AND clause,
the query executes quickly, but looping through the 1700 resulting rows while
simply incrementing a counter takes about 1 minute. If I hardcode the date
instead of use a variable, the query executes quickly even with the last AND
clause.
I'd really like to figure out how to see the query plan on a query executing
within a function. Is this possible? Also, I'm curious why it takes a full
minute or longer (with a significant pause after the first 10 rows and every 50
after that) to loop through 1700 resulting rows. Is there any way to determine
what is going on?
--
Jim Crate
Deep Sky Technologies, Inc.
